<?php
/*
 * (C) Copyright 2013 Torsten Gorath (http://www.blauesdach.de/).
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Contributors:
 *     Torsten Gorath
 */
require_once $basedir."/inc/database.php";
require_once $basedir."/inc/adress.php";
require_once $basedir."/inc/bankverbindung.php";
require_once $basedir."/external/PHPExcel/Classes/PHPExcel.php";

class Mitglieder {
    public $id = "";
    public $adresse_id = "";
    public $bank_id = "";
    public $eingetreten_am;
    public $gekuendigt_am;
    
    public function __construct($argument="") {
		if($argument!='') {
            $temp = Database::getDefault()->openId("mitglieder",$argument);
            foreach($temp as $key => $value) {
                $this->$key = $value;
            }
		}
    }
    
    public function getId() {
        return $this->id;
    }
    
    public function setAdresseId($value) {
        $this->adresse_id = $value;
    }
    
    public function getAdresseId() {
        return $this->adresse_id;
    }
    
	public function getAdress() {
		return new Adresse($this->getAdresseId());
	}
    
    public function setBankId($value) {
        $this->bank_id = $value;
    }
    
    public function getBankId() {
        return $this->bank_id;
    }
	
	public function getBank() {
		return new Bankverbindung($this->getBankId());
	}
    
    public function setEingetretenAm($value) {
        $this->eingetreten_am = $value;
    }
    
    public function getEingetretenAm() {
        return $this->eingetreten_am;
    }
    
    public function setGekuendigtAm($value) {
        $this->gekuendigt_am = $value;
    }
    
    public function getGekuendigtAm() {
        return $this->gekuendigt_am;
    }
    
    public function delete() {
        Database::getDefault()->delete("mitglieder",$this->id);
    }
    
    public function save() {
        $values = array(
            'adresse_id' => $this->adresse_id,
            'bank_id' => $this->bank_id,
            'eingetreten_am' => $this->eingetreten_am,
            'gekuendigt_am' => $this->gekuendigt_am
        );
        if($this->id=="") {
            $this->id = Database::getDefault()->insert("mitglieder", $values);
        } else {
            Database::getDefault()->update("mitglieder", $values, $this->id);
        }
    }
    
    /**
     * get all members of the club. 
     * 
     * @param $aktive default true, get only currently active members
     * @param $end_date default null, mean now or the date to get the member state
     * @return array of Mitglieder instances
     */
    static public function getAll($aktive=true, $end_date=null) {
        global $VEREIN;
        if($end_date != null) {
            $aktive = true;
        }
        $query = "SELECT Mitglieder.* FROM " . $VEREIN['prefix'] . "mitglieder AS Mitglieder, " . $VEREIN['prefix'] . "adresse AS Adresse WHERE Adresse.id=Mitglieder.adresse_id";
        if($aktive) {
            if($end_date != null) {
//                $query .= " AND (eingetreten_am<=".$end_date." AND (gekuendigt_am>".$end_date;
                $query .= " AND (eingetreten_am<='".$end_date."' AND (gekuendigt_am>'".$end_date."'";
            } else {
                $query .= " AND (gekuendigt_am>=NOW()";
            }
            $query .= " OR gekuendigt_am='0000-00-00')";
            if($end_date != null) {
                $query .= ")";
            }
        }
		$query .= " ORDER BY Adresse.nachname, Adresse.vorname";
//        echo $query;
        return Database::getDefault()->execute($query, "Mitglieder");
    }
	
	public static function toExcelHeader($objPHPExcel, $row) {
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Vorname");
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Nachname");
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Strasse");
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "PLZ");
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Strasse");
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "BLZ");
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "Kontonr");
	}
	
	public function toExcelRow($objPHPExcel, $row) {
		$adress = $this->getAdress();
		$bank = $this->getBank();
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $adress->getVorname());
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $adress->getNachname());
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $adress->getStrasse()." ".$adress->getHausnummer().$adress->getZusatzHausnummer());
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $adress->getPlz());
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $adress->getOrt());
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $bank->getBlz());
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $bank->getKontonummer());
	}
	
	public static function toExcel($source) {
		$objPHPExcel = new PHPExcel();
		PHPExcel_Settings::setLocale("de");
		$worksheet = $objPHPExcel->getActiveSheet();
		Mitglieder::toExcelHeader($objPHPExcel, 1);
		for ($i=0; $i < count($source); $i++) {
			$source[$i]->toExcelRow($objPHPExcel,$i+2);
		}
		return $objPHPExcel;
	}
    
}
?>